package DataLayer.DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import DataLayer.Model.Kunde;

public class DBKunde implements IFDBKunde
{
	private Connection con = DBConnection.getInstance().getDBcon();

	@Override
	public ArrayList<Kunde> getAllKunde(boolean klubMedlem)
	{
		int medlem = 0;
		if (klubMedlem)
		{
			medlem = 1;
		}
		return miscWhere("klubmedlem = " + medlem);
	}

	public ArrayList<Kunde> getAllKunde()
	{
		return miscWhere("");
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaName(String name)
	{
		// Implemented for later use
		return miscWhere("fornavn = " + name);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaTlf(String tlf)
	{
		// Implemented for later use
		return miscWhere("tlf = " + tlf);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaEmail(String email)
	{
		// Implemented for later use
		return miscWhere("email = " + email);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaAdresse(String adresse)
	{
		// Implemented for later use
		return miscWhere("adresse = " + adresse);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaLand(String land)
	{
		// Implemented for later use
		return miscWhere("landId_fk = " + land);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaPost(String postnr)
	{
		// Implemented for later use
		return miscWhere("postnr_fk = " + postnr);
	}

	@Override
	public ArrayList<Kunde> getAllKundeViaBy(String by)
	{
		// Implemented for later use
		return miscWhere("bynavn = " + by);
	}

	@Override
	public Kunde getKunde(int kundeId)
	{
		String wClause = "kundeid = '" + kundeId + "'";
		System.out.println("Search " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertKunde(Kunde kunde) throws Exception
	{
		int nextID = GetMax.getMaxId("SELECT MAX(kundeId) FROM Kunde");
		nextID = nextID + 1;
		int rc = -1;
		String query = "INSERT INTO kunde(kundeid, fornavn, efternavn, adresse, postnr_fk, bynavn, "
				+ "landId_fk, tlf, email, klubmedlem, udloebsdato, medlemsstatus, underrettet)  VALUES('"
				+ nextID + "','"
				+ kunde.getFornavn() + "','"
				+ kunde.getEfternavn() + "','"
				+ kunde.getAdresse() + "','"
				+ kunde.getPostnr() + "','"
				+ kunde.getBy() + "','"
				+ kunde.getLand() + "','"
				+ kunde.getTlf() + "','"
				+ kunde.getEmail() + "','"
				+ (kunde.isKlubmedlem() == true ? 1 : 0) + "','"
				+ kunde.getUdloebsdato() + "','"
				+ kunde.getStatus() + "','"
				+ (kunde.isUnderrettet() == true ? 1 : 0) + "')";
		System.out.println("insert : " + query);
		try
		{ // insert new Kunde
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex)
		{
			System.out.println("Kunde was inserted");
		}
		return (rc);
	}

	@Override
	public int updateKunde(Kunde k)
	{
		int rc = -1;
		String query =
				"UPDATE Kunde SET " +
						"fornavn ='" + k.getFornavn() + "', " +
						"efternavn ='" + k.getEfternavn() + "', " +
						"adresse ='" + k.getAdresse() + "', " +
						"postnr_fk ='" + k.getPostnr() + "', " +
						"bynavn ='" + k.getBy() + "', " +
						"landId_fk ='" + k.getLand() + "', " +
						"tlf ='" + k.getTlf() + "', " +
						"email ='" + k.getEmail() + "', " +
						"klubmedlem ='" + (k.isKlubmedlem() == true ? 1 : 0) + "', " +
						"udloebsdato ='" + k.getUdloebsdato() + "', " +
						"medlemsstatus ='" + k.getStatus() + "', " +
						"underrettet ='" + (k.isUnderrettet() == true ? 1 : 0) + "' " +
						" WHERE kundeId = '" + k.getKundeId() + "'";
		System.out.println("Update query:" + query);
		try
		{ // update Kunde
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Update exception in Kunde db: " + ex);
		}
		return (rc);
	}

	@Override
	public int deleteKunde(Kunde k) throws Exception
	{
		int rc = -1;
		String query = "DELETE FROM Kunde WHERE kundeId = '" +
				k.getKundeId() + "'";
		System.out.println(query);
		try
		{ // delete FROM Kunde
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex)
		{
			System.out.println("Delete exception in Kunde db: " + ex);
			throw new Exception();
		}
		return (rc);
	}

	@Override
	public Kunde getKundeViaName(String navn)
	{
		String wClause = "fornavn = '" + navn + "'";
		System.out.println("Search navn " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public Kunde getKundeViaEmail(String email)
	{
		String wClause = "email = '" + email + "'";
		System.out.println("Search " + wClause);
		return singleWhere(wClause);
	}

	private ArrayList<Kunde> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Kunde> list = new ArrayList<Kunde>();
		String query = buildQuery(wClause);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Kunde k = new Kunde();
				k = buildKunde(results);
				list.add(k);
			}
			stmt.close();
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Kunde singleWhere(String wClause)
	{
		ResultSet results;
		Kunde k = null;
		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next())
			{
				k = buildKunde(results);
				stmt.close();
			}
			else
			{
				k = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return k;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT kundeId, fornavn, efternavn, adresse, postnr_fk, bynavn, landId_fk, tlf, email, klubmedlem, udloebsdato, underrettet FROM Kunde ";
		if (wClause.length() > 0)
		{
			query += "WHERE " + wClause;
		}
		return query;
	}

	/**
	 * builds an object k of the class Kunde
	 * 
	 * @return Kunde
	 */
	private Kunde buildKunde(ResultSet results)
	{
		Kunde k = new Kunde();
		try
		{
			k.setKundeId(results.getInt("kundeId"));
			k.setFornavn(results.getString("fornavn"));
			k.setEfternavn(results.getString("efternavn"));
			k.setAdresse(results.getString("adresse"));
			k.setPostnr(results.getString("postnr_fk"));
			k.setBy(results.getString("bynavn"));
			k.setLand(results.getString("landId_fk"));
			k.setTlf(results.getString("tlf"));
			k.setEmail(results.getString("email"));
			k.setKlubmedlem(results.getBoolean("klubmedlem"));
			k.setUdloebsdato(results.getLong("udloebsdato"));
			k.setUnderrettet(results.getBoolean("underrettet"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the Kunde object");
		}
		return k;
	}
}
